﻿/*
=============================================================================
CreatedTime:2011-10-28
Description:Tb_Message三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Models;//引入模型层
namespace DAL
{
	public  partial class Tb_MessageServiceBase
	{
		
		#region 表是否存在该记录
		/// <summary>
		/// Tb_Message表是否存在该记录
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
		public bool ExistsTb_Message(int id)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("Select count(1) from Tb_Message");
			strSql.Append(" where id = @id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", id)
				};
			return DBHelper.Exists(strSql.ToString(),parameters);
		}	
		#endregion
		#region 表增加一条数据
		/// <summary>
		/// Tb_Message表增加一条数据
		/// </summary>
		/// <param name="tb_Message">实体对象</param>
        /// <returns></returns>
		public int AddTb_Message(Tb_Message tb_Message)
		{
			string sql =
			"INSERT Tb_Message(name,tel,Email,Mtype,Mcontent,Hcontent,Hname) "+
			"VALUES (@name,@tel,@Email,@Mtype,@Mcontent,@Hcontent,@Hname)";
			
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
						
					new SqlParameter("@name",tb_Message.Name),
						
					new SqlParameter("@tel",tb_Message.Tel),
						
					new SqlParameter("@Email",tb_Message.Email),
						
					new SqlParameter("@Mtype",tb_Message.Mtype),
						
					new SqlParameter("@Mcontent",tb_Message.Mcontent),
						
					new SqlParameter("@Hcontent",tb_Message.Hcontent),
						
					new SqlParameter("@Hname",tb_Message.Hname)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
	    #region 表删除一条数据
		/// <summary>
		/// Tb_Message表删除一条数据
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
			
		public int DeleteTb_MessageById(int id)	
		{
			string sql="DELETE FROM "+"Tb_Message"+" Where id=@id";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}		
		#endregion
		#region 表修改数据
		/// <summary>
		/// Tb_Message表修改数据
		/// </summary>
		/// <param name="tb_Message">实体对象</param>
        /// <returns></returns>
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public int ModifyTb_Message(Tb_Message tb_Message)
		{
		string sql ="UPDATE "+"Tb_Message"+" set "+
					"name=@name, "+
					"tel=@tel, "+
					"Email=@Email, "+
					"Mtype=@Mtype, "+
					"Mcontent=@Mcontent, "+
					"Hcontent=@Hcontent, "+
					"Hname=@Hname "+ 
				" Where id=@id";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id",tb_Message.Id),
					new SqlParameter("@name",tb_Message.Name),
					new SqlParameter("@tel",tb_Message.Tel),
					new SqlParameter("@Email",tb_Message.Email),
					new SqlParameter("@Mtype",tb_Message.Mtype),
					new SqlParameter("@Mcontent",tb_Message.Mcontent),
					new SqlParameter("@Hcontent",tb_Message.Hcontent),
					new SqlParameter("@Hname",tb_Message.Hname)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}					
		#endregion
		#region 查询数据
		#region 通过sql条件获得数据列表
	    /// <summary>
		/// Tb_Message表通过sql条件获得数据列表
		/// </summary>
		/// <param name="StrWhere"></param>
        /// <returns></returns>
		public DataSet GetDataSetFromTb_Message(string strWhere)
		{
			string strSql="SELECT * FROM Tb_Message";
			if(strWhere.Trim()!="")
			{
				strSql +=" where "+strWhere;
			}
			return DBHelper.Query(strSql.ToString());
		}
		#endregion
		#region 得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Message表得到一个对象实体（泛型集合）
		/// </summary>
        /// <returns>IList</returns>
		public IList<Tb_Message> GetAllTb_Messages()
		{
			string sqlAll = "SELECT * FROM Tb_Message";
			return GetTb_MessagesBySql(sqlAll);
		}
		#endregion
		#region 得到一个对象实体（不是泛型）
	    /// <summary>
		/// Tb_Message表得到一个对象实体
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
		//以标识列获得对象
		public Tb_Message GetTb_MessageById(int id)
		{
			
			string sql = "SELECT * FROM "+"Tb_Message"+" WHERE id = @id";
			try
			{
				SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@id", id));
				if (reader.Read())
				{
					Tb_Message tb_Message = new Tb_Message();
					if(reader["id"]!=DBNull.Value)
						tb_Message.Id=(int)reader["id"];
					if(reader["name"]!=DBNull.Value)
						tb_Message.Name=(string)reader["name"];
					if(reader["tel"]!=DBNull.Value)
						tb_Message.Tel=(string)reader["tel"];
					if(reader["Email"]!=DBNull.Value)
						tb_Message.Email=(string)reader["Email"];
					if(reader["Mtype"]!=DBNull.Value)
						tb_Message.Mtype=(string)reader["Mtype"];
					if(reader["Mcontent"]!=DBNull.Value)
						tb_Message.Mcontent=(string)reader["Mcontent"];
					if(reader["Hcontent"]!=DBNull.Value)
						tb_Message.Hcontent=(string)reader["Hcontent"];
					if(reader["Hname"]!=DBNull.Value)
						tb_Message.Hname=(string)reader["Hname"];
					reader.Close();
					
					return tb_Message;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
		#region 通过sql语句得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Message表得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
        /// <returns>IList</returns>
		public IList<Tb_Message> GetTb_MessagesBySql(string safeSql)
		{
			List<Tb_Message> list = new List<Tb_Message>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_Message tb_Message = new Tb_Message();
					if(row["id"]!=DBNull.Value)
					tb_Message.Id=(int)row["id"];
					if(row["name"]!=DBNull.Value)
					tb_Message.Name=(string)row["name"];
					if(row["tel"]!=DBNull.Value)
					tb_Message.Tel=(string)row["tel"];
					if(row["Email"]!=DBNull.Value)
					tb_Message.Email=(string)row["Email"];
					if(row["Mtype"]!=DBNull.Value)
					tb_Message.Mtype=(string)row["Mtype"];
					if(row["Mcontent"]!=DBNull.Value)
					tb_Message.Mcontent=(string)row["Mcontent"];
					if(row["Hcontent"]!=DBNull.Value)
					tb_Message.Hcontent=(string)row["Hcontent"];
					if(row["Hname"]!=DBNull.Value)
					tb_Message.Hname=(string)row["Hname"];
					list.Add(tb_Message);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句的条件得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Message表通过sql语句的条件（“ｗhere”已经写上）得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="strWhere">strWhere</param>
        /// <returns>IList</returns>
		public IList<Tb_Message> GetTb_MessagesByStrWhere(string strWhere)
		{
			string safeSql="SELECT * FROM Tb_Message";
			if(strWhere.Trim()!="")
			{
				safeSql +=" where "+strWhere;
			}
			List<Tb_Message> list = new List<Tb_Message>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_Message tb_Message = new Tb_Message();
					if(row["id"]!=DBNull.Value)
					tb_Message.Id=(int)row["id"];
					if(row["name"]!=DBNull.Value)
					tb_Message.Name=(string)row["name"];
					if(row["tel"]!=DBNull.Value)
					tb_Message.Tel=(string)row["tel"];
					if(row["Email"]!=DBNull.Value)
					tb_Message.Email=(string)row["Email"];
					if(row["Mtype"]!=DBNull.Value)
					tb_Message.Mtype=(string)row["Mtype"];
					if(row["Mcontent"]!=DBNull.Value)
					tb_Message.Mcontent=(string)row["Mcontent"];
					if(row["Hcontent"]!=DBNull.Value)
					tb_Message.Hcontent=(string)row["Hcontent"];
					if(row["Hname"]!=DBNull.Value)
					tb_Message.Hname=(string)row["Hname"];
					list.Add(tb_Message);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句和参数得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Message表通过sql语句和参数得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
		/// <param name="SqlParameter[]">params SqlParameter[]</param>
        /// <returns>IList</returns>
		public IList<Tb_Message> GetTb_MessagesBySql( string sql, params SqlParameter[] values )
		{
			List<Tb_Message> list = new List<Tb_Message>();
			try
			{
				DataTable table = DBHelper.GetDataTable( sql, values );
				
				foreach (DataRow row in table.Rows)
				{
					Tb_Message tb_Message = new Tb_Message();
					if(row["id"]!=DBNull.Value)
					tb_Message.Id=(int)row["id"];
					if(row["name"]!=DBNull.Value)
					tb_Message.Name=(string)row["name"];
					if(row["tel"]!=DBNull.Value)
					tb_Message.Tel=(string)row["tel"];
					if(row["Email"]!=DBNull.Value)
					tb_Message.Email=(string)row["Email"];
					if(row["Mtype"]!=DBNull.Value)
					tb_Message.Mtype=(string)row["Mtype"];
					if(row["Mcontent"]!=DBNull.Value)
					tb_Message.Mcontent=(string)row["Mcontent"];
					if(row["Hcontent"]!=DBNull.Value)
					tb_Message.Hcontent=(string)row["Hcontent"];
					if(row["Hname"]!=DBNull.Value)
					tb_Message.Hname=(string)row["Hname"];
					list.Add(tb_Message);
				}
				return list;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}	
		}
		#endregion
		
		#region 接收条件和参数Hashtable 返回DataTable
		/// <summary>
		/// Tb_Message表接收条件和参数Hashtable，返回DataTable
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public DataTable GetDataTableFromTb_Message(string strWhere,Hashtable table)
		{
			string strSql = "select * from Tb_Message where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.Query(strSql.ToString(), parmater.ToArray()).Tables[0];
		}
		#endregion
		#region 接收条件和参数Hashtable 返回List泛型集合
		/// <summary>
		/// Tb_Message表接收条件和参数Hashtable，返回List泛型集合
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public IList<Tb_Message> GetListFromTb_Message(string strWhere, Hashtable table)
		{
			List<Tb_Message> models = new List<Tb_Message>();
			string strSql = "select * from Tb_Message where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(),parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					Tb_Message model = new Tb_Message();
					model.Id = Convert.ToInt32(dataReader["id"]);
					model.Name = Convert.ToString(dataReader["name"]);
					model.Tel = Convert.ToString(dataReader["tel"]);
					model.Email = Convert.ToString(dataReader["Email"]);
					model.Mtype = Convert.ToString(dataReader["Mtype"]);
					model.Mcontent = Convert.ToString(dataReader["Mcontent"]);
					model.Hcontent = Convert.ToString(dataReader["Hcontent"]);
					model.Hname = Convert.ToString(dataReader["Hname"]);
					models.Add(model);
				}
			}
			return models;
		}
		#endregion
		#region 接收条件和参数Hashtable，返回一个实体类对象
		/// <summary>
		/// Tb_Message表接收条件和参数Hashtable，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_Message GetModelFromTb_MessageByWhere(string strWhere, Hashtable table)
		{
			Tb_Message model = new Tb_Message();
			string strSql="select * from Tb_Message where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(), parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					model.Id = Convert.ToInt32(dataReader["id"]);
					model.Name = Convert.ToString(dataReader["name"]);
					model.Tel = Convert.ToString(dataReader["tel"]);
					model.Email = Convert.ToString(dataReader["Email"]);
					model.Mtype = Convert.ToString(dataReader["Mtype"]);
					model.Mcontent = Convert.ToString(dataReader["Mcontent"]);
					model.Hcontent = Convert.ToString(dataReader["Hcontent"]);
					model.Hname = Convert.ToString(dataReader["Hname"]);
				}
			}
			return model;
		}
		#endregion
		#region 接收条件，返回一个实体类对象
		/// <summary>
		/// Tb_Message表接收条件，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_Message GetModelFromTb_MessageByWhere(string strWhere)
		{
			Tb_Message model = new Tb_Message();
			string strSql="select * from Tb_Message where "+strWhere+" ";
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString()))
			{
				while (dataReader.Read())
				{
					if(dataReader["id"]!=DBNull.Value)
					model.Id = Convert.ToInt32(dataReader["id"]);
					if(dataReader["name"]!=DBNull.Value)
					model.Name = Convert.ToString(dataReader["name"]);
					if(dataReader["tel"]!=DBNull.Value)
					model.Tel = Convert.ToString(dataReader["tel"]);
					if(dataReader["Email"]!=DBNull.Value)
					model.Email = Convert.ToString(dataReader["Email"]);
					if(dataReader["Mtype"]!=DBNull.Value)
					model.Mtype = Convert.ToString(dataReader["Mtype"]);
					if(dataReader["Mcontent"]!=DBNull.Value)
					model.Mcontent = Convert.ToString(dataReader["Mcontent"]);
					if(dataReader["Hcontent"]!=DBNull.Value)
					model.Hcontent = Convert.ToString(dataReader["Hcontent"]);
					if(dataReader["Hname"]!=DBNull.Value)
					model.Hname = Convert.ToString(dataReader["Hname"]);
				}
			}
			return model;
		}
		#endregion
		
		#region 接收条件和参数Hashtable，删除符合条件的记录
		/// <summary>
		/// Tb_Message表接收条件和参数Hashtable，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_MessageByWhere(string strWhere)
		{
			string strSql="Delete From Tb_Message where "+strWhere+" ";
			return DBHelper.ExecuteCommand(strSql.ToString());
		}
		/// <summary>
		/// Tb_Message表接收条件，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_MessageByWhere(string strWhere, Hashtable table)
		{
			string strSql="Delete From Tb_Message where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.ExecuteCommand(strSql.ToString(), parmater.ToArray());
		}
		#endregion
		#endregion
		#region 分页的方法page为当前页码，NUMCount为每页数据
		
		/// <summary>
		/// 分页的方法page为当前页码，NUMCount为每页数据
		/// </summary>
		/// <param name="page">为当前页码</param>
		/// <param name="NUMCount">为每页数据</param>
        /// <returns></returns>
		public IList<Tb_Message> ShowTb_MessageForPage(int curragePage,int NUMCount)
		{
			int number = 0;
			if (curragePage > 1) {
				number = NUMCount * (curragePage - 1);
			}
			string strsql= "select top "
						   +NUMCount+" * from Tb_Message where  id not in ( select top "
						   +number+" id from Tb_Message order by id desc ) order by id desc";
			return GetTb_MessagesBySql(strsql);
		}
		#endregion
	}
}

